package simorg;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.SQLException;
import java.util.Scanner;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import framework.excel.BasicCell;
import framework.excel.BasicSheet;
import framework.excel.RowRange;

public class ExcelInterface extends DatabaseAccess {

	/**
	 * Test program.
	 */
	public static void main(final String a_args[]) {

		Log.initialize();

		try {
			final ExcelInterface excelInterface = new ExcelInterface(
					"SimOrg.xls");

			excelInterface.doImport();
		} catch (final SQLException sqlex) {
			sqlex.printStackTrace();
		} catch (final IllegalArgumentException iaex) {
			iaex.printStackTrace();
		}
	}

	// the workbook from which the data will be gathered
	private Workbook m_workbook;

	/**
	 * Constructor. Opens the workbook and links to the database.
	 * 
	 * @param a_sourceFileName
	 *            The name of the excel spreadsheet to import.
	 */
	public ExcelInterface(final String a_sourceFileName) {

		// don't allow a non-Excel source
		if (!a_sourceFileName.endsWith("xls")
				&& !a_sourceFileName.endsWith("xlsx")
				&& !a_sourceFileName.endsWith("xlsb")) {

			// TODO: make new exception class for this?
			throw new IllegalArgumentException(
					"Incorrect format for import from Excel spreadsheet.");
		}

		openWorkbook(a_sourceFileName);
	}

	/**
	 * Initialize the tables in the database necessary for the import.
	 */
	private void createTables() throws SQLException {
		for (final Table table : Table.values()) {
			final int tableId = table.ordinal();

			String tableArgs = "";
			for (final String[] arg : VALUES[tableId]) {

				// don't include empty args
				if (arg[VALUE_TYPE] == "") {
					continue;
				}

				tableArgs += arg[VALUE_NAME] + " " + arg[VALUE_TYPE] + ", ";
			}

			if (tableArgs.length() > 0) {
				tableArgs = "("
						+ tableArgs.substring(0, tableArgs.length() - 2) + ")";
				makeTable(TABLE_NAME[tableId], tableArgs);
			}
		}
	}

	/**
	 * Import from the specified Excel spreadsheet to the database.
	 */
	public void doImport() throws SQLException {
		System.out.println("Beginning import to SimOrgDb");

		linkToDatabase();
		createTables();
		populateTables();

		System.out.println("Import to SimOrgDb complete");
	}

	/**
	 * Open the target workbook for performing further operations.
	 * 
	 * @param a_fileName
	 *            The name of the Excel spreadsheet to open.
	 */
	private void openWorkbook(final String a_fileName) {
		FileInputStream fin = null;

		try {

			// create a BasicSheet based on the file
			fin = new FileInputStream(a_fileName);
			m_workbook = WorkbookFactory.create(fin);
			fin.close();
		} catch (final IOException ex) {
			System.out.println("Failed to load workbook file. ");
		} catch (final InvalidFormatException ex) {
			System.out.println("Workbook file is not valid XLS.");
		}
	}

	/**
	 * Populate a single table type on the database from the Excel spreadsheet.
	 * 
	 * @param a_tableType
	 *            The table type (from enum Table) to be populated.
	 */
	private void populateTable(final Table a_tableType) throws SQLException {
		final int tableId = a_tableType.ordinal();
		final int numColumns = NUM_COLUMNS[tableId];

		if (numColumns <= 0) {
			return;
		}

		// get the workbook with values for this table
		final Sheet sheet = m_workbook.getSheet(SHEET_NAME[tableId]);
		final BasicSheet basicSheet = new BasicSheet(m_workbook, sheet);

		// figure out the number of rows in the sheet
		final int numRows = basicSheet.getSheet().getLastRowNum();

		System.out
				.println("******************************************************************");
		System.out.println("Populating " + numRows + " rows in "
				+ TABLE_NAME[tableId] + ".");
		System.out
				.println("******************************************************************");

		// wait for input
		final Scanner sc = new Scanner(System.in);
		// while(!sc.nextLine().equals(""));

		// construct the first half of the statement which will be the same for
		// all inserts
		final String baseStatement = "INSERT INTO " + TABLE_NAME[tableId]
				+ " VALUES";

		// cycle through each row
		for (int row = 1; row < numRows; row++) {
			final RowRange currentRow = basicSheet.getRowRange(row);

			// construct a list of arguments from the columns
			String arguments = "";
			for (int column = 0; column < numColumns; column++) {
				final BasicCell currentCell = currentRow.getCellAndMove();

				final String type = VALUES[tableId][column][VALUE_TYPE];
				String original = "";

				// if the type is empty, skip the column
				if (type == "") {
					continue;
				} else if (type.contains("INT")) {
					original = "" + currentCell.readInt();
				} else {
					original = currentCell.read().toString().replace("'", "''");
				}

				final String formatted = "'" + original + "'";
				arguments += formatted + ", ";
			}
			arguments = "(" + arguments.substring(0, arguments.length() - 2)
					+ ")";
			final String statement = baseStatement + arguments;

			sqlExecute(statement);
		}
	}

	/**
	 * Automate filling all tables on the database with data from the Excel
	 * spreadsheet.
	 */
	private void populateTables() throws SQLException {
		for (final Table table : Table.values()) {
			populateTable(table);
		}
	}
}
